package com.example.homework.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class Initial {
    private final String dbUrl =
            "jdbc:sqlserver://localhost:1433;database=homework;trustServerCertificate=true";
    private final String userName = "sa";
    private final String userPwd = "bjdjr0544";
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private String sql;

    public Map<String, Object> register(int kind, String tele, String name,
                                        int sex, String pwd, String addr) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            if (kind == 0) {
                sql = "select * from Buser where Btele = '" + tele + "' and Bname = '" + name + "'";
                rs = st.executeQuery(sql);
                while (rs.next()) {
                    if (rs.getString("Btele").equals(tele)) {
                        state = 1;
                        break;
                    } else if (rs.getString("Bname").equals(name)) {
                        state = 2;
                        break;
                    }
                }
            } else {
                sql = "select * from Cuser where Ctele = '" + tele + "' and Cname = '" + name + "'";
                rs = st.executeQuery(sql);
                while (rs.next()) {
                    if (rs.getString("Ctele").equals(tele)) {
                        state = 1;
                        break;
                    } else if (rs.getString("Cname").equals(name)) {
                        state = 2;
                        break;
                    }
                }
            }
            if (state == 0) {
                sql = "select Umax from Id_max where temp = 0";
                rs = st.executeQuery(sql);
                rs.next();
                int myId = rs.getInt("Umax") + 1;
                sql = "update Id_max set Umax = " + myId + " where temp = 0";
                st.executeUpdate(sql);
                PreparedStatement prest;
                if (kind == 0) {
                    prest = con.prepareStatement("insert into Buser values ("
                            + myId + ", '" + tele + "', '" + name + "', " + sex +
                            ", '" + pwd + "', null, '" + addr + "', N'未审核', 0)");
                } else {
                    prest = con.prepareStatement("insert into Cuser values ("
                            + myId + ", '" + tele + "', '" + name + "', " + sex +
                            ", '" + pwd + "', null, '" + addr + "', N'未审核', 0)");
                }
                /* Blob blob = con.createBlob();
                blob.setBytes(1, img);
                prest.setBlob(1, blob); */
                prest.executeUpdate();
                sql = "insert into Record(Rtype, Rtime, Rid, Ukind, " +
                        "Utele, Uname, Usex, Upwd, Uaddr) " +
                        "values (1, getdate(), " + myId + ", " + kind + ", '" + tele +
                        "', '" + name + "', " + sex + ", '" + pwd + "', '" + addr + "')";
                st.executeUpdate(sql);
            }
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> login(int kind, String name, String pwd) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            if (kind == 0) {
                sql = "select * from Buser where Bname = '" + name + "' " +
                        "and Bstatus = N'已审核' and Bdel = 0";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    sql = "select * from Buser where Bname = '" + name + "' " +
                            "and Bpwd = '" + pwd + "' and Bstatus = N'已审核' and Bdel = 0";
                    rs = st.executeQuery(sql);
                    if (!rs.next()) {
                        state = 2;
                    }
                } else {
                    state = 1;
                }
            } else if (kind == 2) {
                sql = "select * from Id_max where temp = 0";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    if (!rs.getString("Pwd").equals(pwd)) {
                        state = 2;
                    }
                } else {
                    state = 1;
                }
            } else {
                sql = "select * from Cuser where Cname = '" + name + "' " +
                        "and Cstatus = N'已审核' and Cdel = 0";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    sql = "select * from Cuser where Cname = '" + name + "' " +
                            "and Cpwd = '" + pwd + "' and Cstatus = N'已审核' and Cdel = 0";
                    rs = st.executeQuery(sql);
                    if (!rs.next()) {
                        state = 2;
                    }
                } else {
                    state = 1;
                }
            }
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public int getLogin(int kind, String name, String pwd) {
        int ans = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            if (kind == 0) {
                sql = "select * from Buser where Bname = '" + name + "' " +
                        "and Bpwd = '" + pwd + "' and Bstatus = N'已审核' and Bdel = 0";
                rs = st.executeQuery(sql);
                while (rs.next()) {
                    ans = rs.getInt("Bid");
                }
            } else {
                sql = "select * from Cuser where Cname = '" + name + "' " +
                        "and Cpwd = '" + pwd + "' and Cstatus = N'已审核' and Cdel = 0";
                rs = st.executeQuery(sql);
                while (rs.next()) {
                    ans = rs.getInt("Cid");
                }
            }
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> reset(int kind, String tele, String prepwd,
                                     String pwd, String pwd2) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            if (kind == 0) {
                sql = "select * from Buser where Btele = '" + tele + "' " +
                        "and Bstatus = N'已审核' and Bdel = 0";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    if (!rs.getString("Bpwd").equals(prepwd)) {
                        state = 2;
                    }
                } else {
                    state = 1;
                }
            } else {
                sql = "select * from Cuser where Ctele = '" + tele + "' " +
                        "and Cstatus = N'已审核' and Cdel = 0";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    if (!rs.getString("Cpwd").equals(prepwd)) {
                        state = 2;
                    }
                } else {
                    state = 1;
                }
            }
            if (state == 0) {
                if (!pwd.equals(pwd2)) {
                    state = 3;
                } else {
                    if (kind == 0) {
                        sql = "update Buser set Bpwd = '" + pwd + "' where Btele = '" + tele + "'";
                        st.executeUpdate(sql);
                        sql = "insert into Record(Rtype, Rtime, Rid, Upwd) " +
                                "values (2, getdate(), " + rs.getInt("Bid") + ", '" + pwd + "')";
                    } else {
                        sql = "update Cuser set Cpwd = '" + pwd + "' where Ctele = '" + tele + "'";
                        st.executeUpdate(sql);
                        sql = "insert into Record(Rtype, Rtime, Rid, Upwd) " +
                                "values (2, getdate(), " + rs.getInt("Cid") + ", '" + pwd + "')";
                    }
                    st.executeUpdate(sql);
                }
            }
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }
}
